package com.wu.ssm.spring.utils;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.*;
import java.util.List;
import java.util.Map;
import java.util.Properties;

/**
 * Created by wzf on 2018/11/12/012.
 */
public class JDBCUtils {

    private static final org.slf4j.Logger LOGGER = org.slf4j.LoggerFactory.getLogger(JDBCUtils.class);

    /** 声明一个 Connection类型的静态属性，用来缓存一个已经存在的连接对象 */
    public static final QueryRunner QUERY_RUNNER = new QueryRunner();
    /**
     * 定义一个局部线程变量（使每个线程都拥有自己的连接）
     */
    private static final ThreadLocal<Connection> CONNECTION_THREAD = new ThreadLocal<>();

    private static String driverClassName;
    private static String URL;
    private static String username;
    private static String password;
    private static boolean autoCommit;

    static {
        config();
    }

    private static void config() {
        Properties properties = PropsUtil.loadProps("config.properties");
         /**
          * 获取驱动
          */
        driverClassName = PropsUtil.getString(properties, "jdbc.driver");
        /**
         * 获取URL
         */
        URL = PropsUtil.getString(properties, "jdbc.url");
        /**
         * 获取用户名
         */
        username = PropsUtil.getString(properties, "jdbc.username");
        /**
         * 获取密码
         */
        password = PropsUtil.getString(properties, "jdbc.password");
        /**
         * 设置是否自动提交，一般为false不用改
         */
        autoCommit = false;
    }

    /**
     * 载入数据库驱动类
     */
    private static boolean load(){
        try {
            Class.forName(driverClassName);
            return true;
        } catch (ClassNotFoundException e) {
            System.out.println("驱动类 " + driverClassName + " 加载失败");
        }
        return false;
    }


    /**
     * 建立数据库连接(如果缓存中有直接返回，否则创建)
     */
    public static Connection getConnect() {
        Connection conn = CONNECTION_THREAD.get();
        if (conn == null) {
            /* 加载驱动 */
            load();
            try {
                /* 建立连接 */
                conn = DriverManager.getConnection(URL, username, password);
            } catch (SQLException e) {
                LOGGER.error("获取数据库连接出错！", e);
                throw new RuntimeException(e);
            }finally {
                CONNECTION_THREAD.set(conn);
            }
        }
        return conn;
    }


    /** 提交事务 */
    public static void commitThransation() {
        Connection conn = CONNECTION_THREAD.get();
        if (conn != null) {
            try {
                conn.commit();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /** 回滚事务 */
    public static void rollback() {
        Connection conn = CONNECTION_THREAD.get();
        if (conn != null && !autoCommit) {
            try {
                conn.rollback();
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                closeConnecion();
            }
        }
    }
    /**
     * 建立数据库连接
     */
    public static void closeConnecion() {
        Connection conn = CONNECTION_THREAD.get();
        if(conn != null){
            try {
                conn.close();
            } catch (SQLException e) {
                LOGGER.error("关闭数据库连接失败");
            }finally {
                CONNECTION_THREAD.remove();
            }
        }
    }
    /**
     * 开启事务 即设置自动提交为false
     **/
    public static void beginTransaction() {
        Connection conn = getConnect();
        try {
            conn.setAutoCommit(false);
        } catch (SQLException e) {
            System.out.println("设置事务的提交方式为手动提交失败: " + e.getMessage());
        }

    }

    /**
     * 查询实体列表
     **/
    public static <T> List<T> queryEntityList(Class<T> entityClazz, String sql, Object...params) {
        Connection conn = getConnect();
        List<T> entityList = null;
        try {
            entityList = QUERY_RUNNER.query(conn, sql, new BeanListHandler<T>(entityClazz), params);
            LOGGER.info(sql);
        } catch (SQLException e) {
            LOGGER.error("查询实体列表失败");
        }finally {
            closeConnecion();
        }
        return entityList;
    }


    /**
     * 插入，更新，删除
     **/
    public static int executeUpdate(String sql, Object...params) {
        Connection conn = getConnect();
        int rows = 0;
        try {
            rows = QUERY_RUNNER.update(conn, sql, params);
        } catch (SQLException e) {
            LOGGER.error("插入，更新，删除失败" + e.getMessage());
        } finally {
            //closeConnecion();
        }
        return rows;
    }


    /**
     * 插入实体
     **/
    public static <T> boolean insertEntity(Class<T> entityClazz, Map<String, Object> fieldMap) {
        if(CollectionUtil.isEmpty(fieldMap)){
            LOGGER.error("插入参数不能为空");
            return false;
        }

        String sql = "INSERT INTO " + getTableName(entityClazz);
        StringBuilder columns = new StringBuilder("(");
        StringBuilder values = new StringBuilder("(");
        for (String fieldName :fieldMap.keySet()
             ) {
            columns.append(fieldName).append(", ");
            values.append("?, ");
        }
        columns.replace(columns.lastIndexOf(", "), columns.length(), ")");
        values.replace(values.lastIndexOf(", "), values.length(), ")");
        sql += columns + " VALUES " + values;
        Object[] params = fieldMap.values().toArray();
        return executeUpdate(sql, params) == 1;
    }

    private static <T> String getTableName(Class<T> entityClazz) {
        return entityClazz.getSimpleName().toLowerCase();
    }


    public static Object typeof(Object o) {
        Object r = o;

        if (o instanceof java.sql.Timestamp) {
            return r;
        }
        // 将 java.util.Date 转成 java.sql.Date
        if (o instanceof java.util.Date) {
            java.util.Date d = (java.util.Date) o;
            r = new java.sql.Date(d.getTime());
            return r;
        }
        // 将 Character 或 char 变成 String
        if (o instanceof Character || o.getClass() == char.class) {
            r = String.valueOf(o);
            return r;
        }
        return r;
    }

    /**
     * 释放资源
     **/
    public static void release(Object cloaseable) {

        if (cloaseable != null) {

            if (cloaseable instanceof ResultSet) {
                ResultSet rs = (ResultSet) cloaseable;
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (cloaseable instanceof Statement) {
                Statement st = (Statement) cloaseable;
                try {
                    st.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (cloaseable instanceof Connection) {
                Connection c = (Connection) cloaseable;
                try {
                    c.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

        }

    }

}
